﻿using System;
using System.Data;
using System.Data.SqlTypes;
using System.Globalization;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.IO;

namespace sqlserverextensions.ExportOperations
{
    internal class ExportHelper
    {

        private static string newLine = "\r\n";


        internal static void ValidateMandatoryParameters(SqlString fileName, SqlString sqlStatement, SqlBoolean multiQuery, SqlBoolean columnHeader)
        {

            if (fileName.IsNull || string.IsNullOrEmpty(fileName.ToString()))
                throw new SqlNullValueException("Null or empty destination file path");

            if (sqlStatement.IsNull || String.IsNullOrEmpty(sqlStatement.ToString()))
                throw new SqlNullValueException("Null or empty sql statement");

            if (multiQuery.IsNull)
                throw new SqlNullValueException("Null value of multiQuery parameter");

            if (columnHeader.IsNull)
                throw new SqlNullValueException("Null value of columnHeader parameter");

            ValidateFreespaceInTarget(fileName.ToString());

        }

        internal static void ValidateFreespaceInTarget(string fileName)
        {
            DriveInfo drive = new DriveInfo(Path.GetDirectoryName(fileName));
            //' Freien Platz prüfen; 500 MB sollten es schon noch sein
            if (drive.AvailableFreeSpace < 500000000)
                throw new IOException("To less free space for export");
        }

        internal static void Export2Csv(DataTable dataTable, ref TextWriter strWriter, string fieldDelimiter, string rowDelimiter, Boolean columnHeader)
        {
            if (columnHeader)
                strWriter.Write(GetColumnHeaderCsv(ref dataTable, fieldDelimiter, rowDelimiter));

            foreach (DataRow row in dataTable.Rows)
            {
                strWriter.Write(GetRowDataCsv(dataTable.Columns, row, fieldDelimiter, rowDelimiter));
                strWriter.Flush();
            }

        }


        internal static void Export2Csv(ref SqlDataReader dataReader, ref TextWriter strWriter, string fieldDelimiter, string rowDelimiter, Boolean columnHeader)
        {
            if (columnHeader)
                strWriter.Write(GetColumnHeaderCsv(ref dataReader, fieldDelimiter, rowDelimiter));


            while (dataReader.Read())
            {
                strWriter.Write(GetRowDataCsv(dataReader, fieldDelimiter, rowDelimiter));
                strWriter.Flush();
            }

        }


        internal static void Export2Html(DataTable dataTable, ref TextWriter strWriter, Boolean columnHeader)
        {
            strWriter.Write(GetTableHeaderHtml());

            if (columnHeader)
                strWriter.Write(GetColumnHeaderHtml(ref dataTable));


            foreach (DataRow row in dataTable.Rows)
            {
                strWriter.Write(GetRowDataHtml(dataTable.Columns, row));
                strWriter.Flush();
            }

            strWriter.Write(GetTableFooterHtml());

        }

        internal static void Export2Html(ref SqlDataReader dataReader, ref TextWriter strWriter, Boolean columnHeader)
        {
            strWriter.Write(GetTableHeaderHtml());

            if (columnHeader)
                strWriter.Write(GetColumnHeaderHtml(ref dataReader));

            while (dataReader.Read())
            {
                strWriter.Write(GetRowDataHtml(ref dataReader));
                strWriter.Flush();
            }
            strWriter.Write(GetTableFooterHtml());

        }


        internal static void Export2ExcelXml(DataTable dataTable, ref ExcelXml excXml, Boolean columnHeader)
        {

            if (columnHeader)
                WriteExcelXmlColumnHeader(ref dataTable, ref excXml);

            foreach (DataRow row in dataTable.Rows)
                WriteExcelXmlRow(dataTable.Columns, row, ref excXml);


        }



        internal static void Export2ExcelXml(ref SqlDataReader dataReader, ref ExcelXml excXml, Boolean columnHeader)
        {

            excXml.WorksheetStart("Table");

            if (columnHeader)
                WriteExcelXmlColumnHeader(ref dataReader, ref excXml);

            while (dataReader.Read())
                WriteExcelXmlRow(ref dataReader, ref excXml);

            excXml.WorksheetEnd();

        }



                internal static void Export2Xml(DataTable dataTable, ref Xml xml, Boolean columnHeader){

                    if (columnHeader)
                        WriteXmlColumnHeader( ref dataTable, ref xml);


                    for(int i=0;i<dataTable.Rows.Count;i++)
                        WriteXmlRow(dataTable.Columns, dataTable.Rows[i], ref xml);
                }

        internal static void Export2Xml(ref SqlDataReader dataReader, ref Xml xml, Boolean columnHeader)
        {
            if (columnHeader)
                WriteXmlColumnHeader(ref dataReader, ref xml);


            xml.ElementStart("rows");
            while (dataReader.Read())
                WriteXmlRow(ref dataReader, ref xml);

            xml.ElementEnd();

        }


        internal static string GetRowDataCsv(DataColumnCollection columns, DataRow row, string fieldDelimiter, string rowDelimiter)
        {
            StringBuilder strBuilder = new StringBuilder("");

            foreach (DataColumn col in columns)
                strBuilder.Append(row.ItemArray[col.Ordinal].ToString() + fieldDelimiter);

            strBuilder.Append(rowDelimiter);

            return strBuilder.ToString();
        }
        
        internal static string GetRowDataCsv(SqlDataReader dataReader, string fieldDelimiter, string rowDelimiter)
        {
            StringBuilder strBuilder = new StringBuilder("");

            for (int i = 0; i < dataReader.FieldCount; i++)
                strBuilder.Append(dataReader.GetValue(i).ToString() + fieldDelimiter);

            strBuilder.Append(rowDelimiter);

            return strBuilder.ToString();
        }



        internal static string GetRowDataHtml(DataColumnCollection columns, DataRow row)
        {
            StringBuilder strBuilder = new StringBuilder("");

            strBuilder.Append("<tr>");
            foreach (DataColumn col in columns)
                strBuilder.Append("<td class=\"style3\">" + row.ItemArray[col.Ordinal].ToString() + "</td>");

            strBuilder.Append("</tr>" + newLine);

            return strBuilder.ToString();
        }

        internal static string GetRowDataHtml(ref SqlDataReader dataReader)
        {
            StringBuilder strBuilder = new StringBuilder("");

            strBuilder.Append("<tr>");
            for (int i = 0; i < dataReader.FieldCount; i++)
                strBuilder.Append("<td class=\"style3\">" + dataReader.GetValue(i).ToString() + "</td>");

            strBuilder.Append("</tr>" + newLine);

            return strBuilder.ToString();
        }


        internal static string GetColumnHeaderCsv(ref DataTable dataTable, string fieldDelimiter, string rowDelimiter)
        {
            StringBuilder strBuilder = new StringBuilder(String.Empty);

            foreach (DataColumn col in dataTable.Columns)
                strBuilder.Append(col.ColumnName + fieldDelimiter);

            strBuilder.Append(rowDelimiter);

            return strBuilder.ToString();
        }


        internal static string GetColumnHeaderCsv(ref SqlDataReader dataReader, string fieldDelimiter, string rowDelimiter)
        {
            StringBuilder strBuilder = new StringBuilder(String.Empty);

            for (int i = 0; i < dataReader.FieldCount; i++)
                strBuilder.Append(dataReader.GetName(i) + fieldDelimiter);

            strBuilder.Append(rowDelimiter);

            return strBuilder.ToString();
        }

        internal static string GetColumnHeaderHtml(ref DataTable dataTable)
        {
            StringBuilder strBuilder = new StringBuilder(String.Empty);

            strBuilder.Append("<tr>");
            foreach (DataColumn col in dataTable.Columns)
                strBuilder.Append("<td class=\"style2\">" + col.ColumnName + "</td>");

            strBuilder.Append("</tr>" + newLine);

            return strBuilder.ToString();
        }



        internal static string GetColumnHeaderHtml(ref SqlDataReader dataReader)
        {
            StringBuilder strBuilder = new StringBuilder(String.Empty);

            strBuilder.Append("<tr>");
            for (int i = 0; i < dataReader.FieldCount; i++)
                strBuilder.Append("<td class=\"style2\">" + dataReader.GetName(i) + "</td>");

            strBuilder.Append("</tr>" + newLine);

            return strBuilder.ToString();
        }


        internal static string GetPageHeaderHtml(string title)
        {

            return "<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0 Transitional//EN\"" +
                           "\"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd\">" +
                   "<html xmlns=\"http://www.w3.org/1999/xhtml\">" +
                   "<head>" +
                   "<title>" + title + "</title>" +
                   "<style type=\"text/css\">" +
                          ".style1 {font-weight: bold;background-color: #C0C0C0;} " +
                          ".style2 {border: 1px solid #666666;border-collapse: collapse;} " +
                          ".style3 {border: 1px solid #666666;padding: 1px;} " +
                   "</style>" +
                   "</head>" +
                   "<body><p><br/>" + title + "</p>" + newLine;


        }

        
        
        internal static string GetPageFooterHtml()
        {
            return "</body></html>" + newLine;

        }




        internal static string GetTableHeaderHtml()
        {
            return "<table class=\"style2\">" + newLine;
        }

        internal static String GetTableFooterHtml()
        {
            return "</table>" + newLine;

        }

        internal static void WriteExcelXmlColumnHeader(ref DataTable dataTable, ref ExcelXml excXml)
        {
            excXml.RowStart();
            foreach (DataColumn col in dataTable.Columns)
                excXml.WriteHeaderCell(col.ColumnName);

            excXml.RowEnd();

        }

        internal static void WriteExcelXmlColumnHeader(ref SqlDataReader dataReader, ref ExcelXml excXml)
        {
            excXml.RowStart();
            for (int i = 0; i < dataReader.FieldCount; i++)
                excXml.WriteHeaderCell(dataReader.GetName(i));

            excXml.RowEnd();

        }


        internal static void WriteXmlColumnHeader(ref DataTable dataTable, ref Xml xml)
        {

            xml.ElementStart("columnheaders");
            foreach (DataColumn col in dataTable.Columns)
                xml.WriteHeaderCell(col);

            xml.ElementEnd();

        }

        internal static void WriteXmlColumnHeader(ref SqlDataReader dataReader, ref Xml xml)
        {
            xml.ElementStart("columnheaders");
            for (int i = 0; i < dataReader.FieldCount; i++)
                xml.WriteHeaderCell(dataReader.GetName(i), dataReader.GetProviderSpecificFieldType(i).ToString());

            xml.ElementEnd();

        }


        internal static void WriteExcelXmlRow(DataColumnCollection columns, DataRow row, ref ExcelXml excXml)
        {
            excXml.RowStart();
            foreach (DataColumn col in columns)
                excXml.WriteDataCell(row.ItemArray[col.Ordinal].ToString());

            excXml.RowEnd();
        }


        internal static void WriteExcelXmlRow(ref SqlDataReader dataReader, ref ExcelXml excXml)
        {
            excXml.RowStart();
            for (int i = 0; i < dataReader.FieldCount; i++)
                excXml.WriteDataCell(dataReader.GetValue(i).ToString());

            excXml.RowEnd();
        }


        internal static void WriteXmlRow(DataColumnCollection columns, DataRow row, ref Xml xml)
        {
            xml.ElementStart("row");
            foreach (DataColumn col in columns)
                xml.WriteDataCell(col.ColumnName, row.ItemArray[col.Ordinal].ToString());

            xml.ElementEnd();
        }


        internal static void WriteXmlRow(ref SqlDataReader dataReader, ref Xml xml)
        {
            xml.ElementStart("row");
            for (int i = 0; i < dataReader.FieldCount; i++)
                xml.WriteDataCell(dataReader.GetName(i), dataReader.GetValue(i).ToString());

            xml.ElementEnd();
        }



        internal static DataSet GetDataSet(string sqlStatement)
        {
            DataSet dataSet;
            SqlCommand command;

            using (SqlConnection connection = new SqlConnection("context connection=true"))
            {
                try
                {
                    command = new SqlCommand(sqlStatement, connection);
                    command.CommandType = CommandType.Text;
                    connection.Open();

                    SqlDataAdapter adapter = new SqlDataAdapter(command);

                    //' Ländereinstellung im aktuellen Kontext.
                    dataSet = new DataSet();
                    dataSet.Locale = System.Globalization.CultureInfo.CurrentCulture;
                    adapter.Fill(dataSet);
                    adapter.Dispose();
                    command.Dispose();
                }
                catch (Exception ex)
                {
                    // Laufzeitfehler an den Client zurück geben.
                    throw;
                }
                finally
                {
                    connection.Close();
                }
            }


            return dataSet;

        }

        internal static SqlDataReader GetDataReader(SqlConnection connection, String sqlStatement)
        {
            SqlCommand command = new SqlCommand(sqlStatement, connection);
            return command.ExecuteReader();
        }
    }
}
